﻿using Lg.Core.FileHelper;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Lg.Core.Extensions
{
    public static class ExcelExtensions
    {
        #region 【1】Excel转换为DataTable
        /// <summary>
        /// Excel转换为DataTable，指定数据表或默认第一个数据表
        /// </summary>
        /// <param name="sheetName">指定工作薄名称,不指定可给null值</param>
        /// <param name="fieldMap">excel标题与字段名的对应关系表，如不需要转换则给null值</param>
        /// <param name="action">行格式化或条件判断委拖方法，方法内对datarow的所有操作都将影响封装后的结果</param>
        /// <returns>datatable</returns>
        public static DataTable? ToTable(this Excel excel, string? sheetName = null, Dictionary<string, string>? fieldMap = null,Action<DataRow>? action=null)
        {
            ISheet? sheet = null;
            DataTable? dt = null;
            //Cell列只支持索引取值，所以先把列名与索引建立映射字典，便于后面取值使用
            Dictionary<string, int> cellMap = new Dictionary<string, int>();
            if (excel.Workbook == null)
            {
                throw new Exception("没有可用的excel文档");
            }
            //获取工作表
            //判断是否指定工作表的名称，有则获取指定工作表，无则获取第一个
            if (sheetName != null)
                sheet = excel.Workbook.GetSheet(sheetName);
            else
                sheet = excel.Workbook.GetSheetAt(0);
            //如果表不为null
            if (sheet == null)
            {
                throw new Exception("未找到数据表！");
            }

            IRow row = sheet.GetRow(0);
            int cellCount = row.LastCellNum;//标题总列数（第一行为标题行）
            int rowCount = sheet.LastRowNum;//最后一行的行数
            if (cellCount == 0 || rowCount < 1)
            {
                throw new Exception("没有可转换的行或列!");
            }
            //获取列并保存到datatable
            dt = new DataTable();
            if (fieldMap != null)//fieldMap参数不为空，则封装的table列以fieldMap为主，并按顺序执行，如果找不到数据则留空
            {
                //先按fieldMap的数据和顺序封装cellMap，并赋默认value为-1
                foreach (string key in fieldMap.Keys)
                {
                    cellMap.Add(fieldMap[key], -1);
                }
                //遍历excel标题列，将与fieldMap的key相同的列的索引赋值给cellMap对应的列
                for (int c = 0; c < cellCount; c++)
                {
                    ICell cell = row.GetCell(c);
                    fieldMap.TryGetValue(cell.StringCellValue, out string? cellName);
                    if(cellName != null)
                    {
                        cellMap[cellName] = c;
                    }
                }
            }
            else
            {
                for (int c = 0; c < cellCount; c++)
                {
                    ICell cell = row.GetCell(c);
                    cellMap.Add(cell.StringCellValue,c);
                }
            }
            //遍历行进行数据封装
            for (int r = 0; r <= rowCount; r++)
            {
                row = sheet.GetRow(r);
                if (r == 0)//首行作为table列使用
                {
                    foreach (string key in cellMap.Keys)
                    {
                        DataColumn dc = new DataColumn(key);
                        dt.Columns.Add(dc);
                    }
                }
                else //次行起为数据行
                {
                    DataRow dr = dt.NewRow();
                    foreach (string key in cellMap.Keys)
                    {
                        int _index=cellMap[key];
                        if (_index == -1)
                        {
                            dr[key] = "";
                        }
                        else
                        {
                            ICell dateCell = row.GetCell(cellMap[key]);
                            dr[key] = dateCell==null?"":dateCell.ToString();
                        } 
                    }
                    action?.Invoke(dr);
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
        #endregion

        public static DataTable? ToNoColumnTable(this Excel excel, string? sheetName = null)
        {
            ISheet? sheet = null;
            DataTable? dt = null;
            if (excel.Workbook == null)
            {
                throw new Exception("没有可用的excel文档");
            }
            //获取工作表
            //判断是否指定工作表的名称，有则获取指定工作表，无则获取第一个
            if (sheetName != null)
                sheet = excel.Workbook.GetSheet(sheetName);
            else
                sheet = excel.Workbook.GetSheetAt(0);
            //如果表不为null
            if (sheet == null)
            {
                throw new Exception("未找到数据表！");
            }

            IRow row = sheet.GetRow(0);
            int cellCount = row.LastCellNum;//标题总列数（第一行为标题行）
            int rowCount = sheet.LastRowNum;//最后一行的行数
            if (cellCount == 0 || rowCount < 0)
            {
                throw new Exception("没有可转换的行或列!");
            }
            dt = new DataTable();
            //添加默认列
            for(int cell = 0; cell < cellCount; cell++)
            {
                dt.Columns.Add();
            }
            //遍历行进行数据封装
            for (int r = 0; r <= rowCount; r++)
            {
                row = sheet.GetRow(r);
                DataRow dr = dt.NewRow();
                for(int c = 0; c < cellCount; c++)
                {
                    ICell dateCell = row.GetCell(c);
                    dr[c] = dateCell == null ? "" : dateCell.ToString();
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        #region [2]添加数据表
        /// <summary>
        /// 向Excel的属性WorkBoox添加数据表，如果WorkBook为null则先实例化
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="table">dataTable数据源</param>
        /// <param name="fieldMap">字段映射<数据源字段名，转换后的目标字段名></param>
        /// <param name="sheetName">指定数据表的名称</param>
        /// <returns></returns>
        /// <exception cref="Exception">当数据源为null或无数据实抛异常</exception>
        public static Excel AddSheet(this Excel excel,DataTable table,Dictionary<string,string> fieldMap,string sheetName = "数据表")
        {
            //if (table == null || table.Rows.Count == 0)
            //{
            //    throw new Exception("参数table不包有效数据！");
            //}
            //当没有提供fieldMap映射参数时，将使用范型实例T的属性作为导出列名
            if (fieldMap == null)
            {
                fieldMap = new Dictionary<string, string>();
                foreach (DataColumn col in table.Columns)
                {
                    fieldMap.Add(col.ColumnName, col.ColumnName);
                }
            }
            //执行数据转换
            //【1】创建工作簿
            //【1】创建工作簿
            if (excel.Workbook == null)
            {
                excel.Workbook = new XSSFWorkbook();
            }
            //创建表头信息 ，创建表的属性信息
            //【2】创建工作表sheet
            ISheet _sheet = excel.Workbook.CreateSheet(sheetName);
            //创建行 
            IRow _row = _sheet.CreateRow(0);
            //创建某行的某个单元格(列) row1.CreateCell(0)，设置单元格的内容 row1.CreateCell(0).SetCellValue(1)

            //【3】创建标题列
            int _i = 0; //迭代变量，用于创建excel列
            foreach (KeyValuePair<string, string> kvp in fieldMap)
            {
                _row.CreateCell(_i).SetCellValue(kvp.Value);
                _i++;
            }
            foreach (DataRow dr in table.Rows)
            {
                _row = _sheet.CreateRow(_sheet.LastRowNum + 1);//创建新行
                var _cellNum = 0;//列的迭代变量
                foreach (KeyValuePair<string, string> kvp in fieldMap)
                {
                    string? value = table.Columns[kvp.Key] == null ? "" : Convert.ToString(dr[kvp.Key]);
                    _row.CreateCell(_cellNum).SetCellValue(value);
                    _cellNum++;
                }
            }
            return excel;
        }
        #endregion

        #region [3]添加数据表
        /// <summary>
        /// 向Excel的属性WorkBoox添加数据表，如果WorkBook为null则先实例化
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="list">List数据源</param>
        /// <param name="fieldMap">字段映射<数据源字段名，转换后的目标字段名></param>
        /// <param name="sheetName">指定数据表的名称</param>
        /// <returns>Excel对像，便宜重复执行添加或其它取值操作</returns>
        /// <exception cref="Exception">当数据源为null或无数据实抛异常</exception>
        public static Excel AddSheet<T>(this Excel excel, List<T> list, Dictionary<string, string>? fieldMap, string sheetName = "数据表")
        {
            PropertyInfo[] _propertyInfos = typeof(T).GetProperties();
            if (list == null || list.Count == 0)
            {
                throw new Exception("参数list不包有效数据！");
            }
            //当没有提供fieldMap映射参数时，将使用范型实例T的属性作为导出列名
            if (fieldMap == null)
            {
                fieldMap = new Dictionary<string, string>();
                foreach (PropertyInfo property in _propertyInfos)
                {
                    fieldMap.Add(property.Name, property.Name);
                }
            }
            //执行数据转换
            //【1】创建工作簿
            if (excel.Workbook == null)
            {
                excel.Workbook = new XSSFWorkbook();
            }
            //【2】创建工作表sheet
            ISheet _sheet = excel.Workbook.CreateSheet(sheetName);
            //创建行 
            IRow _row = _sheet.CreateRow(0);
            //创建某行的某个单元格(列) row1.CreateCell(0)，设置单元格的内容 row1.CreateCell(0).SetCellValue(1)

            //【3】创建标题列
            int _i = 0; //迭代变量，用于创建excel列
            foreach (KeyValuePair<string, string> kvp in fieldMap)
            {
                _row.CreateCell(_i).SetCellValue(kvp.Value);
                _i++;
            }
            foreach (var model in list)
            {
                _row = _sheet.CreateRow(_sheet.LastRowNum + 1);//创建新行
                var _cellNum = 0;//列的迭代变量
                foreach (KeyValuePair<string, string> kvp in fieldMap)
                {
                    string value = "";
                    PropertyInfo? property = _propertyInfos.FirstOrDefault(p => p.Name == kvp.Key);
                    if (property != null)
                    {
                        value = Convert.ToString(property.GetValue(model, null)) ?? "";
                    }
                    _row.CreateCell(_cellNum).SetCellValue(value);
                    _cellNum++;
                }
            }
            return excel;
        }
        #endregion

        #region [4]将文件流写入MemoryStream并返回文件byte数组
        /// <summary>
        /// 将文件流写入MemoryStream并返回文件byte数组
        /// </summary>
        /// <param name="excel"></param>
        /// <param name="ms">MemoryStream</param>
        public static byte[] GetBytes(this Excel excel)
        {
            if (excel.Workbook == null)
            {
                throw new Exception("属性Workbook为空！");
            }
            using (MemoryStream ms = new MemoryStream())
            {
                excel.Workbook.Write(ms);
                return ms.ToArray();
            }
        }
        #endregion

        #region [5]保存文件
        public static string Save(this Excel excel)
        {
            string folder = AppContext.BaseDirectory + "temp\\";
            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }
            string filePath =folder+(Guid.NewGuid().GetHashCode().ToString().Replace("-","0"))+".xlsx";
            System.IO.File.WriteAllBytes(filePath, GetBytes(excel));
            return filePath;
        }
        #endregion
    }
}
